package com.wobangkj.common.repository;

import com.wobangkj.common.model.crud.Job;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;
@Component
public interface JobRepository extends JpaRepository<Job, Long> {

    @Query(value = "select id from User where unionid=?1")
    List getIdByUnionid(String unionid);

    @Query(nativeQuery = true, value="select * from  job  where job_name like CONCAT('%',:job_name,'%') ")
    List<Job> findByNameLike(String job_name);

    @Query(nativeQuery = true,value = "select e.*,u.`name`,u.headimg,u.mst_job,u.school,u.grade,u.major,u.college from (select j.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from job j inner join company cp on j.job_company = cp.id group by j.id) e inner join user u on e.job_publisher_id = u.id;") //原生SQL方法
    List<Object> selectAllJobView();

    @Query(nativeQuery = true, value = "select e.*,u.`name`,u.headimg,u.mst_job,u.school,u.grade,u.major,u.college from (select j.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from job j inner join company cp on j.job_company = cp.id group by j.id) e inner join user u on e.job_publisher_id = u.id ORDER BY e.createtime DESC;")
    List<Map> list();

    @Query(nativeQuery = true, value = "select e.*,u.`name`,u.headimg,u.mst_job,u.school,u.grade,u.major,u.college from (select j.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from job j inner join company cp on j.job_company = cp.id group by j.id) e inner join user u on e.job_publisher_id = u.id ORDER BY e.job_collect_num DESC;")
    List<Map> listByNum();

    @Query(nativeQuery = true, value = "select e.*,u.`name`,u.headimg,u.mst_job,u.school,u.grade,u.major,u.college from (select j.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from job j inner join company cp on j.job_company = cp.id group by j.id) e inner join user u on e.job_publisher_id = u.id where e.id= :id ;")
    List<Map> idlist(long id);

    @Query(nativeQuery = true, value = "select headimg,id from user where mst_company_id = :id ;")
    List<Map> headimgList(long id);

    @Query(nativeQuery = true, value = "select e.*,u.`name`,u.headimg,u.mst_job,u.school,u.grade,u.major,u.college from (select j.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from job j inner join company cp on j.job_company = cp.id group by j.id) e inner join user u on e.job_publisher_id = u.id where e.job_name like CONCAT('%',:name,'%') ORDER BY e.createtime DESC ;")
    List<Map> getListByName(String name);

    @Query(nativeQuery = true, value = "select e.*,u.`name`,u.headimg,u.mst_job,u.school,u.grade,u.major,u.college " +
            "from (select j.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from job j inner join company cp " +
            "on j.job_company = cp.id group by j.id) e inner join user u on e.job_publisher_id = u.id " +
            "where e.company_name like CONCAT('%',:company_name,'%') and e.company_num= :company_num and e.company_finance= :company_finance " +
            "and u.college= :college and u.major= :major and e.job_class= :job_class and e.job_class_second= :job_class_second ;")
    List<Map> list(String company_name,Integer company_num,Integer company_finance,Integer college,Integer major,Integer job_class,Integer job_class_second);

    /**
     * jpa 原生sql
     *
     * @param name
     * @param pageable
     * @return
     */
    @Query(nativeQuery = true,
            value = "select * from user " +
                    "where name like CONCAT('%',:name,'%') ")
    Page<Job> findByName(String name, Pageable pageable);

    @Query(nativeQuery = true, value="update job set job_forward_num=job_forward_num+1 where id=:id  ")
    @Modifying
    public void updateJobForward(Long id);
}
